If you want a refresher on Power Query, check out our recent webinar.

Merge in turn

Power Query’s Merge operation allows you to combine data from two or more data sources by reference to a key column. It’s similar to a lookup in regular Excel, only much more powerful. Here’s a basic use case:

The two simple tables on the left tell us customer values and the responsible salespersons; we can merge these using the Customer ID column to create the combined table on the right. This is automated so adding to either table later on will pull through to the combined table.

When creating this merge, we chose a join kind:

The kind used here was “Left Outer” – which we are told is all the rows in the first table, and those with a matching Customer ID from the second table. In previous posts in this series we’ve looked at the “Full Outer” kind – which takes all the rows from both tables. Let’s look at all the options.

Purchase orders and invoices

For this we’ll use these two small datasets:

We’re going to explore what we get out of merging these two tables (the PO one first), through each of these options:

This adds the invoice received data to our PO table where it is available.

Right Outer

As you would expect, this allows us to make the opposite to the Left Outer – a duplicate of the Invoices Received table, showing the PO data where we have it.

Full Outer

A Full Outer brings the full detail from both tables into the merge, allowing us to create a complete invoice table.  This requires a bit more manipulation than the other kinds, as we have to merge the two incomplete Invoice number and Amount columns with custom columns.

Inner

This returns only the three invoices which have been both PO’d and marked as received.

Left Anti

This is a report of all the purchase orders for which we have not received an invoice.

Right Anti

Finally, we have the list of invoices received that we don’t have POs for.

It’s important to think carefully about what join kind is right for your use case – not only to get the right data, but because the ordering of tables can make a difference. While in theory a Right Outer is equivalent to doing a Left Outer with the tables in the opposite order, after any join the left table is always the one that’s fully loaded and the right one is always in a collapsed Table that needs expanding. So it’s much easier to have your primary table as the first (left) table.

You can see all these examples and work through the steps in the Power Query Editor in the attached file .

• Previous post – VBA case study: Capitalising titles
• TOTW index
• Next time – Mail merges!